package com.thinktank.dao;

import java.sql.*;
import java.util.*;
import com.thinktank.vo.*;
import static com.thinktank.util.DbUtil.*;

public class NoticeDao {
	private Connection con = null;

	public NoticeDao(Connection con) {
		super();
		this.con = con;
	}

	public static final int NPG = 5; // 공지사항 페이지별 보일 항목 갯수

	public List<Notice> getAllNotices(int page, String field, String query, String url) {
		int start = 1 + NPG * (page - 1);
		int end = start + NPG - 1;

		PreparedStatement ps = null;
		ResultSet rs = null;
		List<Notice> list = null;

		

		try {
			String sql = null;
			if(url == null){
				sql = "SELECT * FROM" + "(SELECT ROWNUM NUM, TEMP.* FROM"
						+ "(SELECT * FROM NOTICE WHERE (" + field
						+ " LIKE ?)  ORDER BY REGDATE DESC) TEMP) "
						+ "WHERE NUM BETWEEN ? AND ?";
				ps = con.prepareStatement(sql);
				ps.setString(1, "%" + query + "%");
				ps.setInt(2, start);
				ps.setInt(3, end);
			}else{
				sql = "SELECT * FROM" + "(SELECT ROWNUM NUM, TEMP.* FROM"
						+ "(SELECT * FROM NOTICE WHERE (" + field
						+ " LIKE ?) AND (WRITER = (SELECT MB_NO FROM MEMBER WHERE MB_PAGE=?)) ORDER BY REGDATE DESC) TEMP) "
						+ "WHERE NUM BETWEEN ? AND ?";
				ps = con.prepareStatement(sql);
				ps.setString(1, "%" + query + "%");
				ps.setString(2, url);
				ps.setInt(3, start);
				ps.setInt(4, end);
			}
			
			rs = ps.executeQuery();
			// 선 쿼리 처리 후 목록 불러옴

			list = new ArrayList<Notice>();
			while (rs.next()) {
				Notice notice = new Notice();
				notice.setNum(rs.getString("num"));
				notice.setSeq(rs.getString("seq"));
				notice.setTitle(rs.getString("title"));
				notice.setWriter(rs.getString("writer"));
				notice.setContent(rs.getString("content"));
				notice.setHitcount(rs.getInt("hitcount"));
				notice.setRegdate(rs.getDate("regdate"));
				notice.setFilesrc(rs.getString("filesrc"));
				
				list.add(notice);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close(ps);
			close(rs);
		}

		return list;
	};

	public Notice getNotice(String seq) {
		PreparedStatement ps = null;
		ResultSet rs = null;
		Notice notice = null;

		try {
			String sql = "SELECT * FROM NOTICE WHERE SEQ=?";
			ps = con.prepareStatement(sql);
			ps.setString(1, seq);
			rs = ps.executeQuery();

			if(rs.next()){
				notice = new Notice();
				notice.setSeq(rs.getString("seq"));
				notice.setTitle(rs.getString("title"));
				notice.setWriter(rs.getString("writer"));
				notice.setContent(rs.getString("content"));
				notice.setHitcount(rs.getInt("hitcount"));
				notice.setRegdate(rs.getDate("regdate"));
				notice.setFilesrc(rs.getString("filesrc"));
				notice.setPath(rs.getString("path"));
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close(ps);
			close(rs);
		}

		return notice;
	}

	public int update(Notice notice) {
		PreparedStatement ps = null;
		int result = 0;

		try {
			String sql = "UPDATE NOTICE SET TITLE=?, CONTENT=?, REGDATE=SYSDATE WHERE SEQ=?";
			ps = con.prepareStatement(sql);
			ps.setString(1, notice.getTitle());
			ps.setString(2, notice.getContent());
			ps.setString(3, notice.getSeq());
			result = ps.executeUpdate();

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close(ps);
		}
		
		if(result != 0)		commit(con);

		return result;
	}

	public int insert(Notice notice) {
		PreparedStatement ps = null;
		int result = 0;

		try {
			// 1. DB 내에서 MAX SEQ를 받고 +1을 해서 seq 변수에 세팅
			String temp = "select Max(SEQ) from NOTICE";
			ps = con.prepareStatement(temp);
			ResultSet rs = ps.executeQuery();
			int num=0;
			if(rs.next())			num = rs.getInt(1);
			else					num = 0;
			String seq = num + 1 + "";
			for (int i = seq.length(); i < 3; i++) 
				seq = "0".concat(seq);
			close(rs);
			
			// 2. notice정보와 seq로 데이터 삽입
			String sql = "INSERT INTO NOTICE VALUES ("
					+ "?, ?, ?, ?, SYSDATE, 0, ?, ?)";
			ps = con.prepareStatement(sql);
			ps.setString(1, seq);
			ps.setString(2, notice.getTitle());
			ps.setInt(3, Integer.parseInt(notice.getWriter()));
			ps.setString(4, notice.getContent());
			ps.setString(5, notice.getFilesrc());
			ps.setString(6, notice.getPath());
			result = ps.executeUpdate();
			close(ps);
			result = Integer.parseInt(seq);
		} catch (Exception e) {
			e.printStackTrace();
		} 
		if(result != 0)		commit(con);
		return result;
	}

	public int delete(String seq) {
		PreparedStatement ps = null;
		int result = 0;

		try {
			String sql = "DELETE FROM NOTICE WHERE SEQ=?";
			ps = con.prepareStatement(sql);
			ps.setString(1, seq);
			result = ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close(ps);
		}
		if(result != 0)		commit(con);
		return result;
	}

	public int updateHitcount(String seq, int hit) {
		PreparedStatement ps = null;
		int result = 0;

		try {
			String sql = "UPDATE NOTICE SET HITCOUNT=? WHERE SEQ=?";
			ps = con.prepareStatement(sql);
			ps.setInt(1, hit + 1);
			ps.setString(2, seq);
			result = ps.executeUpdate();

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close(ps);
		}
		if(result != 0)		commit(con);
		return result;
	}
	
	public int elementsCount(String field, String query){
		int count = 0;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		try {
			String sql = "SELECT COUNT(*) FROM NOTICE WHERE "+field+" LIKE ?";
			ps = con.prepareStatement(sql);
			ps.setString(1, "%"+query+"%");
			
			rs = ps.executeQuery();
			
			int temp = 0;
			while(rs.next()){
				temp = rs.getInt(1);
			}
			
			count = (int)Math.ceil((double)temp/NPG);

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close(ps);
		}
		
		
		return count;
	}
}
